import javax.naming.InitialContext;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @author 黄忠
 * Created by 黄忠 on 2019-04-02.
 */
@SuppressWarnings("Duplicates")
@WebServlet("/jdbc")
public class MyJdbcServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp){
        try {
                InitialContext context = new InitialContext();
                DataSource dataSource =
                        (DataSource) context.lookup("jdbc/DSTest");
                Connection conn = null;
                Statement stmt = null;
                ResultSet rset = null;
                conn = dataSource.getConnection();
                stmt = conn.createStatement();
                stmt.executeUpdate("CREATE TABLE IF NOT EXISTS `student_course` (\n" +
                        "      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,\n" +
                        "      `number` int(11) unsigned NOT NULL,\n" +
                        "      `course` varchar(30) NOT NULL,\n" +
                        "      `score` int(11) NOT NULL,\n" +
                        "      PRIMARY KEY (`id`)\n" +
                        "    );");
                stmt.executeUpdate("insert into `student_course` (`number`,`course`,`score`) values (1, 'English', 90);" +
                        "insert into `student_course` (`number`,`course`,`score`) values (2, 'Math', 80);" +
                        "insert into `student_course` (`number`,`course`,`score`) values (3, 'Chinese', 85);");
                rset = stmt.executeQuery("select * from student_course order by id desc limit 0,3");

                while(rset.next()) {
                    resp.getWriter().println(rset.getInt("number") + "  " + rset.getString("course") + "  " + rset.getInt("score"));
                }
        }
        catch (Exception e){
            e.printStackTrace();
        }

    }
}
